#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/

$HIVE_CMD -S -f "/root/Liam/online_edu/交付文件/create_test.sql"


${HIVE_HOME} -S -e "
--创建表
drop table if exists signup_dws.customer_singup_day_count;
create table if not exists signup_dws.customer_singup_day_count
(
  dt                      string   comment'日粒度',
  month                   string   comment'月粒度',
  year                    string   comment'年粒度',
  school_id               string   comment'学校id',
  school_name             string   comment'校名',
  subject_id              string   comment'学科id',
  subject_name            string   comment'学科名',
  tdepart_id              bigint   comment '直属部门(咨询中心)',
  name                    string   comment'部门名称',
  origin_channel          string   comment '来源渠道',
  origin_type             string   comment'数据来源',
  intention_cnt           bigint   comment'意向人数',
  signup_cnt              bigint   comment'报名人数',
  clue_cnt                bigint   comment'线上有效线索人数',
  type                    string   comment'标签'
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');



drop table if exists signup_dm.customer_singup_alldim_ct;
 create table if not exists  signup_dm.customer_singup_alldim_ct(
  dt                      string   comment'日粒度',
  month                   string   comment'月粒度',
  year                    string   comment'年粒度',
  school_id               string   comment'学校id',
  school_name             string   comment'校名',
  subject_id              string   comment'学科id',
  subject_name            string   comment'学科名',
  tdepart_id              bigint   comment '直属部门(咨询中心)',
  name                    string   comment'部门名称',
  origin_channel          string   comment '来源渠道',
  origin_type             string   comment'数据来源',
  signup_amt              bigint   comment'报名人数',
  intention_amt           bigint   comment'意向人数',
  clue_amt                bigint   comment'线上有效线索人数',
  intention_present       float    comment'意向报名率',
  clue_present            float    comment'有效线索报名率',
  type                    string   comment'标签',
  time_type               string   comment'时间标签'
)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');


"
${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

--插入数据
insert into  signup_dws.customer_singup_day_count
select
       dt as day_,
       substring(dt,1,7) year_month ,
       substring(dt,1,4) year_ ,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       tdepart_id,
       name,
       origin_channel,
       origin_type,

        count(id) as yixiang,--意向人数
       -- 每天线上线下的意向转报名率 = 报名人数 / 意向人数
        count(if(payment_state='PAID',1,NULL)) as baoming,--报名人数

        COUNT(IF(appeal_status<>'Effective' and origin_type='online',1,NULL)) as xiansuo,--线索人数

       -- 每天线上的有效线索报名转换率 = 报名人数 / 有效线索人数
case when grouping (itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name)=0
       then '校区学科'
       when grouping (itcast_subject_id,itcast_subject_name)=0
       then '学科'
       when grouping (itcast_school_id,itcast_school_name)=0
       then'学校'
       when grouping (origin_channel)=0
       then '渠道来源'
       when grouping (tdepart_id,name)=0
       then'咨询中心'
       when grouping (origin_type)=0
       then '数据来源'
       else '时间'
       end  type

from hive.signup_dwb.customer_singup_de
group by
grouping sets(
    (dt,itcast_school_id,itcast_school_name),
    (dt,itcast_school_id,itcast_school_name,origin_type),
    (dt,itcast_subject_id,itcast_subject_name,origin_type),
    (dt,itcast_school_id,itcast_school_name,itcast_subject_id,itcast_subject_name,origin_type),
    (dt,origin_channel,origin_type),
    (dt,tdepart_id,name,origin_type),
    (dt,origin_type));

insert into signup_dm.customer_singup_alldim_ct
select dt,
       "month",
       "year",
       school_id,
       school_name,
       subject_id,
       subject_name,
       tdepart_id,
       name,
       origin_channel,
       origin_type,
       signup_amt,
       intention_amt,
       clue_amt,
       intention_present,
       clue_present,
       new_type,
       time_type
from (
select
       dt,
       "month",
       "year",
       school_id,
       school_name,
       subject_id,
       subject_name,
       tdepart_id,
       name,
       origin_channel,
       origin_type,
       TYPE,
       SUM(signup_cnt)signup_amt,
       SUM(intention_cnt)intention_amt,
       SUM(clue_cnt)clue_amt,
       if (sum(intention_cnt)=0,0,sum (signup_cnt)*1.00/SUM(intention_cnt)) intention_present,
       if (SUM(clue_cnt)=0,0,SUM (signup_cnt)*1.00/SUM(clue_cnt)) clue_present,

       case
       when grouping (TYPE,school_id,school_name,subject_id,subject_name)=0
       then '校区学科'
       when grouping (TYPE,subject_id,subject_name)=0
       then '学科'
       when grouping (TYPE,school_id,school_name)=0
       then'学校'
       when grouping (TYPE,origin_channel)=0
       then '渠道来源'
       when grouping (TYPE,tdepart_id,name)=0
       then'咨询中心'
       when grouping (TYPE,origin_type)=0
       then '数据来源'
       else '时间'
       end as new_type,

       case
       when grouping("year","month",dt) = 0
       then 'date'
       when grouping("month",dt) = 0
       then 'month'
       when grouping("year") = 0
       then 'year'
       end as time_type

from hive.signup_dws.customer_singup_day_count


group by
grouping sets(
--年
    (TYPE,"year","month",dt,school_id,school_name),
    (TYPE,"year","month",dt,school_id,school_name,origin_type),
    (TYPE,"year","month",dt,subject_id,subject_name,origin_type),
    (TYPE,"year","month",dt,school_id,school_name,subject_id,subject_name,origin_type),
    (TYPE,"year","month",dt,origin_channel,origin_type),
    (TYPE,"year","month",dt,tdepart_id,name,origin_type),
    (TYPE,"year","month",dt,origin_type),
--月TYPE,
    (TYPE,"month",dt,school_id,school_name),
    (TYPE,"month",dt,school_id,school_name,origin_type),
    (TYPE,"month",dt,subject_id,subject_name,origin_type),
    (TYPE,"month",dt,school_id,school_name,subject_id,subject_name,origin_type),
    (TYPE,"month",dt,origin_channel,origin_type),
    (TYPE,"month",dt,tdepart_id,name,origin_type),
    (TYPE,"month",dt,origin_type),
--日TYPE,
    (TYPE,dt,school_id,school_name),
    (TYPE,dt,school_id,school_name,origin_type),
    (TYPE,dt,subject_id,subject_name,origin_type),
    (TYPE,dt,school_id,school_name,subject_id,subject_name,origin_type),
    (TYPE,dt,origin_channel,origin_type),
    (TYPE,dt,tdepart_id,name,origin_type),
    (TYPE,dt,origin_type)))a
where type = new_type;"
